package service;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

import db.DBConn;
import entities.GoodsEntity;
import entities.UserEntity;

public class GoodsService {

	// 获得销售量前N个商品
	public static List<GoodsEntity> getGoods(int n) throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods ,shop_seller,shop_goodsclass  where  goods_sellerId = seller_id "
				+ " and goods_classId = class_id and goods_state = 1 order by goods_saleCount desc limit "
				+ n;

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next() && n > 0) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setClassName(rs.getString("class_name"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

			goodsList.add(goods);

			n--;
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 获得某一类别的商品
	public static List<GoodsEntity> getClassGoods(String classId, String limit)
			throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and goods_classId = "
				+ classId
				+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1 order by rand() limit "
				+ limit;
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

			goodsList.add(goods);
		}
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 获得某一商家的商品
	public static List<GoodsEntity> getSellerGoods(String id)
			throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and  shop_goods.goods_sellerId = "
				+ id
				+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1  order by goods_id";
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

			goodsList.add(goods);
		}
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 获得特定商品
	public static GoodsEntity getPointGoods(int id) throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods , shop_seller , shop_goodsclass where shop_goods.goods_sellerId = shop_seller.seller_id and goods_classId = class_id and goods_id = "
				+ id;
		GoodsEntity goods = new GoodsEntity();
		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setKeyWords(rs.getString("goods_keyWords"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));
		}
		rs.close();
		stmt.close();
		conn.close();
		return goods;
	}

	// 针对搜索
	public static List<GoodsEntity> searchResult(String classKey,
			String priceKey, String key) throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = " select shop_goods.*,seller_name,class_name from shop_goods LEFT JOIN shop_seller  on seller_id = goods_sellerId , shop_goodsclass"
				+ " where goods_state=1 and goods_classId = class_id and ";
		if ("all".equals(classKey)) {
			sql += " ( goods_name like '%" + key + "%' or class_name like '%"
					+ key + "%' or seller_name like '%" + key + "%' )";
		} else {
			sql += classKey + " like '%" + key + "%'";
		}
		sql += " and goods_price " + priceKey + " group by goods_id;";

		System.out.println(sql);

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));
			goods.setClassName(rs.getString("class_name"));
			goodsList.add(goods);
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 针对首页搜索
		public static List<GoodsEntity> indexSearchResult(String key) throws SQLException {
			List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
			Connection conn = DBConn.getConn();
			Statement stmt = conn.createStatement();
			String sql = " select * from shop_goods , shop_seller , shop_goodsclass where goods_sellerId = seller_id and goods_classId = class_id and goods_state=1 and ( goods_name like '%" + key + "%' or goods_keyWords like '%" + key +"%' or class_name like '%" + key + "%' )";
			System.out.println(sql);
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				GoodsEntity goods = new GoodsEntity();
				goods.setState(rs.getInt("goods_state"));
				goods.setId(rs.getInt("goods_id"));
				goods.setClassId(rs.getInt("goods_classId"));
				goods.setName(rs.getString("goods_name"));
				goods.setSellerId(rs.getInt("goods_sellerId"));
				goods.setSellerName(rs.getString("seller_name"));
				goods.setSaleClass(rs.getInt("goods_saleClass"));
				goods.setPrice(rs.getDouble("goods_price"));
				goods.setDescrition(rs.getString("goods_desc"));
				goods.setDiscount(rs.getDouble("goods_discount"));
				goods.setImageUrl(rs.getString("goods_image"));
				goods.setRank(rs.getDouble("goods_rank"));
				goods.setSaleCount(rs.getInt("goods_saleCount"));
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));
				goods.setClassName(rs.getString("class_name"));
				goodsList.add(goods);
			}
			rs.close();
			stmt.close();
			conn.close();
			return goodsList;
		}
	
	public static List<GoodsEntity> getHotGoods() throws SQLException {
		return getGoods(7);
	}

	public static List<GoodsEntity> getAllGoods() throws SQLException {
		return getGoods(Integer.MAX_VALUE);
	}

	// 用于往数据库中添加商品（测试）
	public static int insertGoods(int classId, String goodsName, int sellerId,
			double price, String img, String desc) throws SQLException {

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "INSERT INTO `shop_goods` VALUES (null, '" + classId
				+ "', '" + goodsName + "', '" + sellerId + "', '1', '" + price
				+ "', '0', '" + img + "', '富含蛋白质与维生素', '快餐,蛋,番茄,米饭', '" + desc
				+ "', '5.0', '0', '" + sdf.format(new Date()) + "' ,'" + img
				+ "','" + img + "',1,'" + sdf.format(new Date()) + "');";
		int result = stmt.executeUpdate(sql);
		return result;
	}

	// 用于往数据库中添加商品
	public static String addGoods(String sellerId, String goodsName,
			String classId, String price, String discount, String imgUrl,
			String keywords, String desc) throws SQLException {

		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "INSERT INTO shop_goods VALUES (null, '" + classId
				+ "', '" + goodsName + "', '" + sellerId + "', '1', '" + price
				+ "'," + discount + ", '" + imgUrl + "_1.png', '好营养哦', '"
				+ keywords + "', '" + desc + "', 5.0, 0, '"
				+ sdf.format(new Date()) + "' ,'" + imgUrl + "_2.png','"
				+ imgUrl + "_3.png',1,'" + sdf.format(new Date()) + "');";
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";
	}

	// 获得分页的商品
	public static List<GoodsEntity> getPageGoods(String key, String cls,
			String price, String sellerId, String pageIndex)
			throws SQLException, UnsupportedEncodingException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and  shop_goods.goods_sellerId = "
				+ sellerId
				+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1 ";

		if (!"all".equals(price)) {
			String[] prices = price.split("-");
			sql += " and goods_price  between " + prices[0] + "  and  "
					+ prices[1];
		}
		if (!"all".equals(cls)) {
			sql += " and goods_classId = " + cls;
		}
		if (!"all".equals(key)) {
			key = java.net.URLDecoder.decode(key, "UTF-8");
			sql += " and (goods_name like '%" + key
					+ "%' or goods_keyWords like '%" + key + "%' ) ";
		}
		sql += " order by goods_id limit " + (Integer.parseInt(pageIndex) * 8)
				+ " ,8";

		System.out.println("####" + sql + "###");

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setSellerName(rs.getString("seller_name"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goods.setClassName(rs.getString("class_name"));
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

			goodsList.add(goods);
		}
		stmt.close();
		conn.close();
		return goodsList;
	}
	
	// 获得分页的商品2
		public static List<GoodsEntity> getPageGoods2(String hot, String sellerId, String pageIndex)
				throws SQLException, UnsupportedEncodingException {
			List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
			Connection conn = DBConn.getConn();
			Statement stmt = conn.createStatement();
			String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and  shop_goods.goods_sellerId = "
					+ sellerId
					+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1 ";

			sql += " order by goods_saleCount Desc limit " + (Integer.parseInt(pageIndex) * 8)
					+ " ,8";

			System.out.println("####" + sql + "###");

			ResultSet rs = stmt.executeQuery(sql);

			while (rs.next()) {
				GoodsEntity goods = new GoodsEntity();
				goods.setState(rs.getInt("goods_state"));
				goods.setId(rs.getInt("goods_id"));
				goods.setClassId(rs.getInt("goods_classId"));
				goods.setName(rs.getString("goods_name"));
				goods.setSellerId(rs.getInt("goods_sellerId"));
				goods.setSaleClass(rs.getInt("goods_saleClass"));
				goods.setPrice(rs.getDouble("goods_price"));
				goods.setDescrition(rs.getString("goods_desc"));
				goods.setDiscount(rs.getDouble("goods_discount"));
				goods.setImageUrl(rs.getString("goods_image"));
				goods.setSellerName(rs.getString("seller_name"));
				goods.setRank(rs.getDouble("goods_rank"));
				goods.setSaleCount(rs.getInt("goods_saleCount"));
				goods.setClassName(rs.getString("class_name"));
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

				goodsList.add(goods);
			}
			stmt.close();
			conn.close();
			return goodsList;
		}
		
		// 获得分页的商品3
				public static List<GoodsEntity> getPageGoods3(String comment, String sellerId, String pageIndex)
						throws SQLException, UnsupportedEncodingException {
					List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
					Connection conn = DBConn.getConn();
					Statement stmt = conn.createStatement();
					String sql = "select * from shop_goods,shop_seller,shop_goodsclass where goods_classId = class_id and  shop_goods.goods_sellerId = "
							+ sellerId
							+ " and shop_goods.goods_sellerId = shop_seller.seller_id and goods_state=1 ";

					sql += " order by goods_rank Desc limit " + (Integer.parseInt(pageIndex) * 8)
							+ " ,8";

					System.out.println("####" + sql + "###");

					ResultSet rs = stmt.executeQuery(sql);

					while (rs.next()) {
						GoodsEntity goods = new GoodsEntity();
						goods.setState(rs.getInt("goods_state"));
						goods.setId(rs.getInt("goods_id"));
						goods.setClassId(rs.getInt("goods_classId"));
						goods.setName(rs.getString("goods_name"));
						goods.setSellerId(rs.getInt("goods_sellerId"));
						goods.setSaleClass(rs.getInt("goods_saleClass"));
						goods.setPrice(rs.getDouble("goods_price"));
						goods.setDescrition(rs.getString("goods_desc"));
						goods.setDiscount(rs.getDouble("goods_discount"));
						goods.setImageUrl(rs.getString("goods_image"));
						goods.setSellerName(rs.getString("seller_name"));
						goods.setRank(rs.getDouble("goods_rank"));
						goods.setSaleCount(rs.getInt("goods_saleCount"));
						goods.setClassName(rs.getString("class_name"));
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
						goods.setOnSaleTime(sdf.format(rs.getTimestamp("goods_onSaleTime")));

						goodsList.add(goods);
					}
					stmt.close();
					conn.close();
					return goodsList;
				}

	// 测试用，修改商品图片路径
	public static int changeGoodsImg(String oldUrl, String newUrl)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();

		String sql = "update shop_goods set goods_image = '" + newUrl
				+ "_1.png' , goods_image2 = '" + newUrl
				+ "_2.png' , goods_image3 = '" + newUrl
				+ "_3.png' where goods_image ='" + oldUrl + "' ";
		System.out.println(sql + "-------sql");
		int result = stmt.executeUpdate(sql);
		return result;
	}

	// 获得“购买了上述商品的用户还购买了。。。。”

	// 获得特定商品
	public static List<GoodsEntity> getOtherCartGoods(String userName)
			throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		UserEntity user = UserService.getUser(userName);
		String sql = "select * from shop_goods , shop_orderitem where item_userId != "
				+ user.getId()
				+ " and goods_id = item_goodsId and goods_state = 1 order by rand() limit 5";

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goodsList.add(goods);
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	// 更新商品信息OEM++ 2013-05-09 liwt
	public static String changeGoodsInfo(String sellerId, String goodsId,
			String goodsName, String goodsClass, String goodsPrice,
			String goodsDiscount, String goodsKeywords, String goodsDesc)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt;
		stmt = conn.createStatement();

		String sql = "update shop_goods set goods_name = '" + goodsName
				+ "' , goods_classId= " + goodsClass + " , goods_price = "
				+ goodsPrice + " ,goods_discount = " + goodsDiscount
				+ " ,goods_keyWords = '" + goodsKeywords + "' ,goods_desc = '"
				+ goodsDesc + "' where goods_Id =" + goodsId
				+ " and goods_sellerId=" + sellerId;
		System.out.println(sql + "-------sql");
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";

	}

	// 得到新建商品图片的图片名称 OEM++liwt 2013-05-09
	public static String getNewGoodsName(String sellerId) {
		Connection conn = DBConn.getConn();
		Statement stmt;
		String imgName = "seller" + sellerId.trim();

		try {
			stmt = conn.createStatement();
			String sql = "select goods_id from shop_goods order by goods_id desc limit 1";
			System.out.println(sql + "-------sql");
			ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				imgName += "g" + (rs.getInt(1) + 1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return imgName;
	}

	// 下架某商品
	public static String offlineGoods(String sellerId, String goodsId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt;
		stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "update shop_goods set goods_state = 2 , goods_offlineTime = '"
				+ sdf.format(new Date())
				+ "' where goods_Id ="
				+ goodsId
				+ " and goods_sellerId=" + sellerId;
		System.out.println(sql + "-------sql");
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";
	}

	// 已下架的商品重新上架
	public static String onlineGoods(String sellerId, String goodsId)
			throws SQLException {
		Connection conn = DBConn.getConn();
		Statement stmt;
		stmt = conn.createStatement();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String sql = "update shop_goods set goods_state = 1, goods_offlineTime = '"
				+ sdf.format(new Date())
				+ "' where goods_Id ="
				+ goodsId
				+ " and goods_sellerId=" + sellerId;
		System.out.println(sql + "-------sql");
		int result = stmt.executeUpdate(sql);
		return result > 0 ? "success" : "failed";
	}
	
	// 判断客户是否买过该商品
		public static boolean isBuy(String userName,int goodsId)
				throws SQLException {
			Connection conn = DBConn.getConn();
			Statement stmt;
			stmt = conn.createStatement();
			String sql = "select * from shop_goods,shop_user,shop_orderitem where goods_id = item_goodsId and item_userId = user_id and goods_id="+goodsId+" and user_name =  '"+userName+"'";
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next())
				return true;
			else
				return false;
		}

	public static List<GoodsEntity> getOfflineGoods(String sellerId)
			throws SQLException {
		List<GoodsEntity> goodsList = new ArrayList<GoodsEntity>();
		Connection conn = DBConn.getConn();
		Statement stmt = conn.createStatement();
		String sql = "select * from shop_goods where goods_sellerId = "
				+ sellerId + " and goods_state = 2 order by goods_offlineTime";

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		ResultSet rs = stmt.executeQuery(sql);

		while (rs.next()) {
			GoodsEntity goods = new GoodsEntity();
			goods.setState(rs.getInt("goods_state"));
			goods.setOfflineTime(sdf.format(rs
					.getTimestamp("goods_offlineTime")));
			goods.setId(rs.getInt("goods_id"));
			goods.setClassId(rs.getInt("goods_classId"));
			goods.setName(rs.getString("goods_name"));
			goods.setSellerId(rs.getInt("goods_sellerId"));
			goods.setSaleClass(rs.getInt("goods_saleClass"));
			goods.setPrice(rs.getDouble("goods_price"));
			goods.setDescrition(rs.getString("goods_desc"));
			goods.setDiscount(rs.getDouble("goods_discount"));
			goods.setImageUrl(rs.getString("goods_image"));
			goods.setRank(rs.getDouble("goods_rank"));
			goods.setSaleCount(rs.getInt("goods_saleCount"));
			goodsList.add(goods);
		}
		rs.close();
		stmt.close();
		conn.close();
		return goodsList;
	}

	public static void main(String args[]) throws SQLException {
		System.out.println("getallgoods----------------\n" + getAllGoods());
		System.out.println("getClassgoods-------------------\n"
				+ getClassGoods("1", "5"));
		System.out.println("getgoods----------------\n" + getGoods(10));
		System.out.println("getgoods-------------------\n" + getHotGoods());
		System.out.println("getOthergoods----------------\n"
				+ getOtherCartGoods("liwentao"));
		System.out.println("getOfflinegoods----------------\n"
				+ getOfflineGoods("1"));

	}
}
